package com.jwpdk.dbframework;

import java.io.StringReader;
import java.sql.PreparedStatement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import com.jwpdk.appframework.data.DataMap;
import com.jwpdk.appframework.data.IData;
import com.jwpdk.util.StringUtils;
/**
 * @author 王志伟
 */
public class SQL {

	private final Logger		log			= Logger.getLogger(this.getClass());

	// 占位符起始标记
	private static final String	PARAM_SIGN	= ":";
	private static final String	REF_SIGN	= "?";
	// 参数属性
	private IData				param;
	// 是否需要解析SQL 默认否
	private boolean				isDecode	= false;
	// 字段集合
	private String[]			names;
	// 字段对应的参数集合
	private IData				list		= new DataMap();
	// 独立参数集合
	private Object[]			paramArray;
	// SQL
	private StringBuffer		sqlStr		= new StringBuffer();

	public SQL() {
	}

	public SQL(String sql) {
		sqlStr = new StringBuffer(sql);
	}

	/**
	 * 设置一个新的SQL,重置参数集合
	 * @param sql
	 * @param flag 是否重置参数
	 */
	public void setSql(String sql, boolean flag) {
		if ( flag )
			clear();
		this.sqlStr = new StringBuffer(sql);
	}

	/**
	 * 清除参数集合及SQL对象
	 */
	public void clear() {
		sqlStr = new StringBuffer();
		isDecode = false;
		list.clear();
		paramArray = null;
		names = null;
		if ( param != null ) {
			param.clear();
			param = null;
		}
	}

	/**
	 * 拼接SQL
	 * @param sql
	 */
	public SQL append(String sql) {
		this.sqlStr.append(sql);
		return this;
	}

	/**
	 * 设置参数属性
	 * @param name 参数名称
	 * @param value 参数值
	 * @param type 参数类型
	 */
	@SuppressWarnings("unchecked")
	public void set(String name, Object value, int type) {
		param = new DataMap();
		param.put("NAME",name);
		param.put("VALUE",value);
		param.put("TYPE",type);
		list.put(name,param);
	}

	/**
	 * 默认设置为字符串类型
	 * @param name
	 * @param value
	 */
	public void set(String name, Object value) {
		this.set(name,value,Types.VARCHAR);
	}

	/**
	 * 针对不需要解析的SQL 设置参数处理
	 * @param param
	 */
	public void set(Object[] param) {
		paramArray = param;
	}

	@Override
	public String toString() {
		return "SQL:"+sqlStr.toString()+"\r\n参数集合：" + list.toString() + "\r\n 字段集合：" + Arrays.toString(names);
		
	}

	/**
	 * 是否为带参数的SQL
	 * @return true 是，false 否
	 */
	public boolean isParamSql() {
		String sql = sqlStr.toString();
		return sql.indexOf(PARAM_SIGN) > 0 || sql.indexOf(REF_SIGN) > 0;
	}

	/**
	 * 设置参数将SQL中?位置绑定参数
	 */
	public void fillParams(PreparedStatement statement) throws Exception {

		if ( names == null && isDecode ) {
			getAskSql();
		}
		if ( log.isDebugEnabled() ) {
			log.debug("param：" + list.toString());
		}
		// 通过名称绑定参数的处理
		log.debug("isDecode：" + isDecode);
		if ( isDecode ) {
			for (int i = 0; i < names.length; i++) {
				Object value = list.get(names[i]);
				if ( StringUtils.IsEmptyOrNull(value) ) {
					statement.setNull(i + 1,Types.VARCHAR);
					log.debug("[" + (i + 1) + "]BINDING [" + names[i] + "] IS null");
				} else {
					if ( value instanceof IData ) {
						IData iData = (IData) value;
						if ( StringUtils.IsEmptyOrNull(iData.get("VALUE")) ) {
							statement.setNull(i + 1,Types.VARCHAR);
							log.debug("[" + (i + 1) + "]字段[" + names[i] + "] IS null");
						} else if ( Integer.parseInt(iData.getString("TYPE")) == Types.LONGVARCHAR ) {
							statement.setCharacterStream(i + 1,new StringReader(iData.getString("VALUE").toString()),iData.getString("VALUE").toString().length());
							log.debug("[" + (i + 1) + "]字段[" + names[i] + "] 绑定值 [" + iData.getString("VALUE").toString() + "]");
						} else {
							statement.setObject(i + 1,iData.get("VALUE"));
							log.debug("[" + (i + 1) + "]字段[" + names[i] + "] 绑定值 [" + iData.getString("VALUE").toString() + "]");
						}

					}
				}
			}
		} else {
			for (int i = 0; i < paramArray.length; i++) {
				Object value = paramArray[i];
				if ( StringUtils.IsEmptyOrNull(value) ) {
					statement.setNull(i + 1,Types.VARCHAR);
					log.debug("[" + (i + 1) + "]BINDING IS null");
				} else {
					statement.setObject(i + 1,value);
				}
			}
		}
	}

	/**
	 * 解析SQL
	 * @param sqlstr
	 * @throws Exception
	 */
	public String getAskSql() throws Exception {
		String sqlstr = sqlStr.toString();
		if ( sqlstr.indexOf(PARAM_SIGN) > 0 ) {
			isDecode = true;
			if ( log.isDebugEnabled() ) {
				log.debug("原始sql：" + sqlstr);
			}
			class QuoteRange {

				int		start;

				int		length;

				String	text;
			}
			Pattern patParam = Pattern.compile("(:[a-zA-Z_0-9\\$]*)");
			Pattern patQuote = Pattern.compile("('[^']*')");
			Matcher matcher;
			List< QuoteRange> quoteRanges = new ArrayList< QuoteRange>();
			matcher = patQuote.matcher(sqlstr);
			while ( matcher.find() ) {
				QuoteRange r = new QuoteRange();
				r.start = matcher.start();
				r.text = matcher.group();
				r.length = r.text.length();
				quoteRanges.add(r);
			}
			matcher = patParam.matcher(sqlstr);
			List< String> keys = new ArrayList< String>();
			while ( matcher.find() ) {
				String key = matcher.group().substring(1);
				if ( !quoteRanges.isEmpty() ) {
					boolean skip = false;
					int pos = matcher.start();
					Iterator it = quoteRanges.iterator();
					while ( it.hasNext() ) {
						QuoteRange r = (QuoteRange) it.next();
						if ( pos >= r.start && pos < r.start + r.length ) {
							skip = true;
							break;
						}
					}
					if ( skip ) {
						continue;
					}
				}
				sqlstr = sqlstr.replaceFirst(":" + key.replaceAll("\\Q$\\E","\\\\\\$"),"?");
				keys.add(key);
			}

			this.names = (String[]) keys.toArray(new String[0]);
			if ( log.isDebugEnabled() ) {
				log.debug("解析sql：" + sqlstr);
				log.debug("字段名称：" + Arrays.toString(names));
			}
		}
		return sqlstr;
	}

}
